package com.fy.tt.dao.impl;

import com.fy.toolhelper.db.BaseDaoImpl;
import com.fy.tt.dao.SentenceDao;
import com.fy.tt.entity.Sentence;
import com.fy.tt.entity.query.SentenceQuery;
import com.fy.tt.entity.vo.SentenceVo;
import org.springframework.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class SentenceDaoImpl extends BaseDaoImpl<Sentence> implements SentenceDao {

    @Override
    public List<SentenceVo> getAllSentences(Connection connection) throws SQLException {
        String sql = "select s.*,p.band_name from bls_sentence s left join bls_parser p on s.band_obj_id=p.band_obj_id";
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        ArrayList<SentenceVo> sentences = new ArrayList<>();
        while(rs.next()){
            SentenceVo sentence = new SentenceVo(rs);
            sentences.add(sentence);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return sentences;
    }


    @Override
    public int deleteSentence(Connection connection, int id) throws SQLException {
        String sql = "delete from bls_sentence where id = ?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, id);
        int i = pstm.executeUpdate();
        if (pstm != null){
            pstm.close();
        }
        return i;
    }


    @Override
    public List<Sentence> getPageSentences(Connection connection, int pageSize, int current) throws SQLException{
        int start = pageSize * (current - 1);
        String sql = "select * from bls_sentence limit ?,?";
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, start);
        pstm.setInt(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<Sentence> sentences = new ArrayList<>();
        while(rs.next()){
            Sentence sentence = new Sentence(rs);
            sentences.add(sentence);
        }
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return sentences;
    }

    @Override
    public List<SentenceVo> getConditionPageSentences(Connection connection, SentenceQuery sentenceQuery, int pageSize, int current) throws SQLException{
        int start = pageSize * (current - 1);
        String sql = " select s.*,p.band_name from bls_sentence s left join bls_parser p on s.band_obj_id=p.band_obj_id where 1=1 ";
        if(!StringUtils.isEmpty(sentenceQuery.getName())){
            sql+=" and ( s.name like '%" + sentenceQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + sentenceQuery.getName() + "%'";
            sql+=" or s.description like '%" + sentenceQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(sentenceQuery.getIsTypeIn())){
            sql+=" and s.is_type_in = " + sentenceQuery.getIsTypeIn() ;
        }
        if(!StringUtils.isEmpty(sentenceQuery.getBandObjId())){
            sql+=" and s.band_obj_id = " + sentenceQuery.getBandObjId() ;
        }
        if(StringUtils.isEmpty(sentenceQuery.getName())
                && StringUtils.isEmpty(sentenceQuery.getIsTypeIn())
                && StringUtils.isEmpty(sentenceQuery.getBandObjId())){
            sql = " select s.*,p.band_name from bls_sentence s left join bls_parser p on s.band_obj_id=p.band_obj_id limit ? , ? ";
        }else{
            sql += " limit ? , ?";
        }
        PreparedStatement pstm = connection.prepareStatement(sql);
        pstm.setInt(1, start);
        pstm.setInt(2, pageSize);
        ResultSet rs = pstm.executeQuery();
        ArrayList<SentenceVo> sentences = new ArrayList<>();
        while(rs.next()){
            SentenceVo sentence = new SentenceVo(rs);
            sentences.add(sentence);
        }
        System.out.println(sentences);
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return sentences;
    }

    @Override
    public int getTotalSentence(Connection connection, SentenceQuery sentenceQuery) throws SQLException {
        int count = 0;
        String sql = " select s.*,p.band_name from bls_sentence s left join bls_parser p on s.band_obj_id=p.band_obj_id where 1=1 ";
        if(!StringUtils.isEmpty(sentenceQuery.getName())){
            sql+=" and ( s.name like '%" + sentenceQuery.getName() + "%'";
            sql+=" or p.band_name like '%" + sentenceQuery.getName() + "%'";
            sql+=" or s.description like '%" + sentenceQuery.getName() + "%' )";
        }
        if(!StringUtils.isEmpty(sentenceQuery.getIsTypeIn())){
            sql+=" and s.is_type_in = " + sentenceQuery.getIsTypeIn() ;
        }
        if(!StringUtils.isEmpty(sentenceQuery.getBandObjId())){
            sql+=" and s.band_obj_id = " + sentenceQuery.getBandObjId() ;
        }
        if(StringUtils.isEmpty(sentenceQuery.getName())
                && StringUtils.isEmpty(sentenceQuery.getIsTypeIn())
                && StringUtils.isEmpty(sentenceQuery.getBandObjId())){
            sql = " select s.*,p.band_name from bls_sentence s left join bls_parser p on s.band_obj_id=p.band_obj_id";
        }
        PreparedStatement pstm = connection.prepareStatement(sql);
        ResultSet rs = pstm.executeQuery();
        rs.last();
        count = rs.getRow();
        if(rs != null){
            rs.close();
        }
        if (pstm != null){
            pstm.close();
        }
        return count;
    }

    public SentenceDaoImpl() throws Exception {
        super();
    }
}